---
title: Add column
description: An add column operation creates a new column on an existing table.
---

## Structure

<YamlJsonTabs>
```yaml
add_column:
  table: name of table to which the column should be added
  up: SQL expression
  column:
    name: name of column
    type: postgres type
    comment: postgres comment for the column
    nullable: true|false
    unique: true|false
    pk: true|false
    default: default value for the column
    check:
      name: name of check constraint
      constraint: constraint expression
    references:
      name: name of foreign key constraint
      table: name of referenced table
      column: name of referenced column
      on_delete: ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION
```
```json
{
  "add_column": {
    "table": "name of table to which the column should be added",
    "up": "SQL expression",
    "column": {
      "name": "name of column",
      "type": "postgres type",
      "comment": "postgres comment for the column",
      "nullable": true|false,
      "unique": true|false,
      "pk": true|false,
      "default": "default value for the column",
      "check": {
        "name": "name of check constraint",
        "constraint": "constraint expression"
      },
      "references": {
        "name": "name of foreign key constraint",
        "table": "name of referenced table",
        "column": "name of referenced column",
        "on_delete": "ON DELETE behaviour, can be CASCADE, SET NULL, RESTRICT, or NO ACTION. Default is NO ACTION",
      }
    }
  }
}
```
</YamlJsonTabs>

The `up` SQL is used when a row is added to the old version of the table (ie, without the new column). In the new version of the table, the row's value for the new column is determined by the `up` SQL expression. The `up` SQL can be omitted if the new column is nullable or has a `DEFAULT` defined.

Default values are subject to the usual rules for quoting SQL expressions. In particular, string literals should be surrounded with single quotes.

**NOTE:** As a special case, the `up` field can be omitted when adding `smallserial`, `serial` and `bigserial` columns.

### Volatile and non-volatile defaults

Postgres handles adding columns with defaults in one of two ways, depending on the [volatility](https://www.postgresql.org/docs/current/xfunc-volatility.html) of the default expression:
  * For non-volatile defaults, Postgres uses a fast-path optimization to avoid a table rewrite.
  * For volatile defaults, Postgres must rewrite the table to populate the new column with the default value. This requires a lengthy `ACCESS_EXCLUSIVE` lock on the table.

When adding a new column with a `default`, `pgroll` handles these two cases differently:

For non-volatile defaults, `pgroll` will add the column with the default value directly on migration start.

For volatile defaults, `pgroll` will add the column without the `default` on migration start and use the `up` SQL (which must be set to the same expression as `default`) to populate the column with the 'default' value. The default will be added to the column on migration completion.

By handling defaults in this way, `pgroll` ensures that the lengthy `ACCESS_EXCLUSIVE` lock is avoided when adding columns with volatile defaults.

## Examples

### Add multiple columns

Add three new columns to the `products` table. Each column addition is a separate operation:

<ExampleSnippet example="03_add_column.yaml" languange="yaml" />

### Add column with `up` SQL

Add a new column to the `users` table and define `up` SQL to ensure that the new column is populated with a value when a row is added to the old version of the table:

<ExampleSnippet example="06_add_column_to_sql_table.yaml" languange="yaml" />

### Add column without `up` SQL

Add a new column to the `reviews` table. There is no need for `up` SQL because the column has a default:

<ExampleSnippet example="17_add_rating_column.yaml" languange="yaml" />

### Add column with `CHECK` constraint

Add a new column to the `people` table with a `CHECK` constraint defined:

<ExampleSnippet
  example="26_add_column_with_check_constraint.yaml"
  languange="yaml"
/>

### Add column with comment

Add a new column to the `people` table with a comment defined on the new column:

<ExampleSnippet example="30_add_column_simple_up.yaml" languange="yaml" />

### Add column with a user defined type

Add a new column to the `fruits` table that has an enum type, defined in an earlier migration:

<ExampleSnippet example="41_add_enum_column.yaml" languange="yaml" />
